#!/usr/bin/python
#coding=utf-8

from sqlalchemy import create_engine
from sqlalchemy.types import VARCHAR,INTEGER,DECIMAL
import pandas as pd
import datetime
from sqlalchemy.pool import NullPool
from common import db_utils as util
import sys

dtypedict = {'ACCT': INTEGER,
            'COST_CENTER': VARCHAR,'UNIT_CODE': VARCHAR,'CLASS': VARCHAR,'SHIFT': VARCHAR,
            'PRODUCE_TIME': VARCHAR,'PROD_COILNO': VARCHAR,'ENTRY_COILNO': VARCHAR,'STEELNO': VARCHAR,
            'ACT_TOTAL_COST': DECIMAL,'MAIN_MATERIAL_COST': DECIMAL,'ENERGYCONSUM_COST': DECIMAL,
            'ACCES_COST': DECIMAL,'ROLL_COST': DECIMAL,'OTHER_COST': DECIMAL,
            'COST_6': DECIMAL,'COST_7': DECIMAL,'COST_8': DECIMAL,
            'ACT_OUTPUT_WT': DECIMAL,'ACT_INPUT_WT': DECIMAL,'THICK': VARCHAR,'THICK_CODE': VARCHAR,
            'WIDTH': VARCHAR, 'WIDTH_CODE': VARCHAR, 'ANNEAL_CURVE': VARCHAR, 'TRIM_FLAG': VARCHAR,
            'ENTRY_WIDTH': VARCHAR, 'ENTRY_WIDTH_CODE': VARCHAR, 'ENTRY_THICK': VARCHAR, 'ENTRY_THICK_CODE': VARCHAR,
            'PLAN_NO_C': VARCHAR, 'PLAN_NO_CODE': VARCHAR, 'TRIMMING_WIDTH_RANGE': VARCHAR, 'TRIMMING_WIDTH_CODE': VARCHAR,
            'IN_MAT_DIV_CODE': VARCHAR, 'IN_MAT_DIV': VARCHAR, 'PRODUCE_NICK_FLAG': VARCHAR, 'PROD_CAT_CODE': VARCHAR,
            'PROD_CAT': VARCHAR, 'REC_CREATOR': VARCHAR, 'REC_CREATE_TIME': VARCHAR, 'REC_REVISOR': VARCHAR,'REC_REVISOR_TIME': VARCHAR
            }

def getConnectionDb2(host, port, dbname, user, password):
    # conn = pg.connect(host=host, port=port, dbname=dbname, user=user, password=password)
    engine = create_engine('ibm_db_sa://' + user + ':' + password + '@' + host + ':' + str(port) + '/' + dbname,
                           encoding="utf-8", poolclass=NullPool)
    return engine.connect()

def save_as_table(df,table_name,conn,schema_name=None,dtypedict=None):
    return pd.io.sql.to_sql(df, table_name, conn,schema_name,if_exists='append',index=False,chunksize=10000,dtype=dtypedict)

def toCD0201(unit_code,start_time,end_time,p_data_type):
    # 先删除这段时间的数据
    del_sql = """delete from BGTARAS1.T_ADS_FACT_SICB_CD0201 WHERE UNIT_CODE='""" + str(unit_code) + """' and PRODUCE_TIME BETWEEN '""" + str(start_time) + """' AND '""" + str(end_time) + """'"""
    conn_rds.execute(del_sql)

    sql_rds = """
        SELECT
                    A.ACCT AS ACCT,
                    A.COST_CENTER,
                    A.UNIT_CODE,
                    A.CLASS,
                   A.SHIFT,
                   A.PRODUCE_TIME,
                   A.PROD_COILNO,
                    A.ENTRY_COILNO,A.STEELNO,
                    CASE WHEN SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                    ELSE CAST(SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)<>'15' THEN COALESCE(A.UNIT_PROC_ON_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END)
                    +(CAST(SUM(CASE WHEN LEFT(A.COST_SUBJECT,2) IN ('15') THEN COALESCE(A.UNIT_PROC_ON_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0)ELSE 0 END) - SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.COST_SUBJECT_PRICE,0) ELSE 0 END))
                    END AS ACT_TOTAL_COST,
                    CASE WHEN SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                    ELSE CAST(SUM(CASE WHEN LEFT(A.COST_SUBJECT,2) IN ('15') THEN COALESCE(A.UNIT_PROC_ON_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0)ELSE 0 END) - SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.COST_SUBJECT_PRICE,0) ELSE 0 END) END AS MAIN_MATERIAL_COST,--主原料
                    CASE WHEN SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                    ELSE CAST(SUM(CASE WHEN LEFT(A.COST_SUBJECT,1) IN ('4','5') THEN COALESCE(A.UNIT_PROC_ON_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0)ELSE 0 END) END AS ENERGYCONSUM_COST ,--能源
                    CASE WHEN SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                    ELSE CAST(SUM(CASE WHEN LEFT(A.COST_SUBJECT,1) IN ('6') THEN COALESCE(A.UNIT_PROC_ON_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0)ELSE 0 END) END AS ACCES_COST ,--辅料
                    CASE WHEN SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                    ELSE CAST(SUM(CASE WHEN LEFT(A.COST_SUBJECT,1)='3' AND LEFT(A.COST_SUBJECT,2) NOT IN ('39') THEN COALESCE(A.UNIT_PROC_ON_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0)ELSE 0 END) END AS ROLL_COST ,
                    CASE WHEN SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                    ELSE CAST(SUM(CASE WHEN A.COST_SUBJECT='20000'  THEN COALESCE(A.UNIT_PROC_ON_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0)ELSE 0 END) END AS OTHER_COST ,--人工费
                    CASE WHEN SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                    ELSE CAST(SUM(CASE WHEN LEFT(A.COST_SUBJECT,1)='7'  THEN COALESCE(A.UNIT_PROC_ON_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0)ELSE 0 END) END AS COST_6 ,--协力费
                    CASE WHEN SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                    ELSE CAST(SUM(CASE WHEN A.COST_SUBJECT IN ('71200', '71100','88781','88782','88785','88787','88788')  THEN COALESCE(A.UNIT_PROC_ON_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0)ELSE 0 END) END AS COST_7 ,--运输费
                    0.0 AS COST_8,
                    SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_OUTPUT_WT,0) ELSE 0 END) AS ACT_OUTPUT_WT,
                    SUM(CASE WHEN LEFT(A.COST_SUBJECT,2)='15' THEN COALESCE(A.ACT_INPUT_WT,0) ELSE 0 END) AS ACT_INPUT_WT,
                    A.THICK,A.THICK_CODE,
                    A.WIDTH,A.WIDTH_CODE,
                    A.ANNEAL_CURVE,
                        A.TRIM_FLAG,
                        A.ENTRY_WIDTH,
                        A.ENTRY_WIDTH_CODE,
                        A.ENTRY_THICK,
                        A.ENTRY_THICK_CODE,
                        A.PLAN_NO_C,
                        A.PLAN_NO_CODE,
                        A.TRIMMING_WIDTH_RANGE,
                        A.TRIMMING_WIDTH_CODE,
                        A.IN_MAT_DIV_CODE ,
                        A.IN_MAT_DIV,
                        A.PRODUCE_NICK_FLAG,
                        A.PROD_CAT_CODE,
                        A.PROD_CAT,
                        'BGTARAS1' AS REC_CREATOR,
                        TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_CREATE_TIME,
                        '' AS REC_REVISOR,
                        TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_REVISOR_TIME
                FROM BGTARAS1.T_ADS_FACT_SICB_CA0001 A
                    where A.DATA_TYPE='""" + p_data_type + """'
                        AND '1001' = A.ACCT
                        AND A.PRODUCE_TIME >= '""" + start_time + """'
                        AND A.PRODUCE_TIME<= '""" + end_time + """' AND A.APPTHROWAIMODE LIKE '%PN%'AND A.UNIT_CODE='""" + unit_code + """'
                GROUP BY
                    A.ACCT,
                    A.DEPARTMENT_CODE,
                    A.COST_CENTER,
                    A.UNIT_CODE,
                    A.CLASS,A.SHIFT,
                    A.ENTRY_COILNO,
                    A.PROD_COILNO,
                    A.PRODUCE_TIME,A.WIDTH,A.WIDTH_CODE,A.THICK,A.THICK_CODE,A.STEELNO,
                    A.ANNEAL_CURVE,
                        A.TRIM_FLAG,
                        A.ENTRY_WIDTH,
                        A.ENTRY_WIDTH_CODE,
                        A.ENTRY_THICK,
                        A.ENTRY_THICK_CODE,
                        A.PLAN_NO_C,
                        A.PLAN_NO_CODE,
                        A.TRIMMING_WIDTH_RANGE,
                        A.TRIMMING_WIDTH_CODE,
                        A.PLAN_NO_CODE,
                        A.IN_MAT_DIV_CODE,
                        A.IN_MAT_DIV,
                        A.PRODUCE_NICK_FLAG,
                        A.PROD_CAT_CODE,
                        A.PROD_CAT
    """
    # print(sql)
    df = util.query(conn_rds, sql_rds)
    util.insert(df, conn_rds, "T_ADS_FACT_SICB_CD0201", "BGTARAS1", dtypedict)


def singleCD0201(unit_code,start_time,end_time,p_data_type):
    global conn_db2, conn_rds
    conn_db2 = util.getConnectionDb2('10.70.16.170', '50170 ', 'dbprode7', 'dbprode7', 'dbprode7')
    conn_rds = util.getConnectionDb2('10.70.48.121', '50000 ', 'RG0PROD', 'bgtaroq000', 'bgtaroq000bgta')
    toCD0201(unit_code,start_time,end_time,p_data_type)
    conn_rds.close()
    conn_db2.close()
    

def CD0201(start_time,end_time,p_data_type):
    global conn_db2, conn_rds
    conn_db2 = util.getConnectionDb2('10.70.16.170', '50170 ', 'dbprode7', 'dbprode7', 'dbprode7')
    conn_rds = util.getConnectionDb2('10.70.48.121', '50000 ', 'RG0PROD', 'bgtaroq000', 'bgtaroq000bgta')

    #4期
    toCD0201('Q602', start_time, end_time, '0')
    toCD0201('Q512', start_time, end_time, '0')
    toCD0201('Q414', start_time, end_time, '0')
    toCD0201('Q503', start_time, end_time, '0')

    toCD0201('Q314', start_time, end_time, '0')
    toCD0201('Q412', start_time, end_time, '0')
    toCD0201('Q402', start_time, end_time, '0')
    toCD0201('Q502', start_time, end_time, '0')
    toCD0201('Q403', start_time, end_time, '0')
    toCD0201('Q518', start_time, end_time, '0')
    toCD0201('Q161', start_time, end_time, '0')
    toCD0201('Q162', start_time, end_time, '0')
    toCD0201('Q163', start_time, end_time, '0')
    toCD0201('Q165', start_time, end_time, '0')
    toCD0201('Q173', start_time, end_time, '0')
    toCD0201('Q174', start_time, end_time, '0')
    toCD0201('Q175', start_time, end_time, '0')

    toCD0201('Q114', start_time, end_time, '0')
    toCD0201('Q214', start_time, end_time, '0')
    toCD0201('Q112', start_time, end_time, '0')
    toCD0201('Q212', start_time, end_time, '0')
    toCD0201('Q312', start_time, end_time, '0')
    toCD0201('Q103', start_time, end_time, '0')
    toCD0201('Q203', start_time, end_time, '0')
    toCD0201('Q303', start_time, end_time, '0')
    toCD0201('Q418', start_time, end_time, '0')
    toCD0201('Q164', start_time, end_time, '0')
    toCD0201('Q171', start_time, end_time, '0')
    toCD0201('Q172', start_time, end_time, '0')

    toCD0201('C302', start_time, end_time, '0')
    toCD0201('Q102', start_time, end_time, '0')
    toCD0201('Q202', start_time, end_time, '0')
    toCD0201('Q302', start_time, end_time, '0')
    toCD0201('Q116', start_time, end_time, '0')
    toCD0201('Q216', start_time, end_time, '0')
    toCD0201('Q101', start_time, end_time, '0')
    toCD0201('H050', start_time, end_time, '0')

    conn_rds.close()
    conn_db2.close()

if __name__ == '__main__':
    '''先读配置表
      再获取RDS连接
    '''
    now_time = datetime.datetime.now()
    start_time = (now_time - datetime.timedelta(minutes=360)).strftime("%Y%m%tmp_dict%H%M%S")
    end_time = now_time.strftime("%Y%m%tmp_dict%H%M%S")
    # start_time = '20210508200000'
    # end_time = '20210509200000'
    global conn_db2,conn_rds
    conn_db2 = util.getConnectionDb2('10.70.16.170', '50170 ', 'dbprode7', 'dbprode7', 'dbprode7')
    conn_rds = util.getConnectionDb2('10.70.48.121', '50000 ', 'RG0PROD', 'bgtaroq000', 'bgtaroq000bgta')
    toCD0201("Q418",start_time,end_time,'D')
    conn_rds.close()
    conn_db2.close()
    sys.exit()